/************************************************************
https://anotherssisframework.codeplex.com

Copyright (c) 2014, Allen J. Zyck <sql.architect@outlook.com>

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated 
documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights 
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons
 to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the 
Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, 
INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE 
AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, 
DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, 
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.


************************************************************/

/***********

File: CreateFrameworkEnvironment.sql

Description:
This is a sql template to create the standard environment for the project packages. Generally, on a single
ETL server, you'll have one of these environments to support all the packages in a project.

After creating the environment, read permissions will need to be assigned to the ETL service account.

***********/


EXEC SSISDB.[catalog].create_environment @environment_name=N'SSISFrameworkCommon', @environment_description=N'Common inputs for each SSISDB Project', @folder_name=N'<SSISDB Folder/Project, sysname, SSIS Project Name>'

GO

EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'ConfigDB Database Name', @sensitive=False, @description=N'Database name of Config DB', @environment_name=N'SSISFrameworkCommon', @folder_name=N'<SSISDB Folder/Project, sysname, SSIS Project Name>', @value=N'<Configuration DB Name, sysname, DWConfig>', @data_type=N'String'
GO

EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'ConfigDB Instance Name', @sensitive=False, @description=N'Instance name for Config DB', @environment_name=N'SSISFrameworkCommon', @folder_name=N'<SSISDB Folder/Project, sysname, SSIS Project Name>', @value=N'<Configuration Instance, sysname, localhost>', @data_type=N'String'
GO

EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'Data Warehouse Database Name', @sensitive=False, @description=N'Database name of the Data Warehouse', @environment_name=N'SSISFrameworkCommon', @folder_name=N'<SSISDB Folder/Project, sysname, SSIS Project Name>', @value=N'<Data Warehouse DB Name, sysname, DataWarehouse>', @data_type=N'String'
GO

EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'Data Warehouse Instance Name', @sensitive=False, @description=N'Instance name for DataWarehouse DB', @environment_name=N'SSISFrameworkCommon', @folder_name=N'<SSISDB Folder/Project, sysname, SSIS Project Name>', @value=N'<Data Warehouse Instance, sysname, localhost>', @data_type=N'String'
GO

EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'HistoryDB Database Name', @sensitive=False, @description=N'Database name of History DB', @environment_name=N'SSISFrameworkCommon', @folder_name=N'<SSISDB Folder/Project, sysname, SSIS Project Name>', @value=N'<History DB Name, sysname, DWStaging>', @data_type=N'String'
GO

EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'HistoryDB Instance Name', @sensitive=False, @description=N'Instance name of History DB', @environment_name=N'SSISFrameworkCommon', @folder_name=N'<SSISDB Folder/Project, sysname, SSIS Project Name>', @value=N'<History Instance, sysname, localhost>', @data_type=N'String'
GO

EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'SMTP Package Failure From Line', @sensitive=False, @description=N'The From e-mail address for packages failures', @environment_name=N'SSISFrameworkCommon', @folder_name=N'<SSISDB Folder/Project, sysname, SSIS Project Name>', @value=N'<Failed package FROM e-mail, sysname, etlserver@abc.def>', @data_type=N'String'
GO

EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'SMTP Package Failure To Line', @sensitive=False, @description=N'Email address in To Line for packages failures', @environment_name=N'SSISFrameworkCommon', @folder_name=N'<SSISDB Folder/Project, sysname, SSIS Project Name>', @value=N'<Failed package TO e-mail, sysname, SSISFailure@abc.def>', @data_type=N'String'
GO

EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'SMTP Package Success From Line', @sensitive=False, @description=N'The From e-mail address for package success', @environment_name=N'SSISFrameworkCommon', @folder_name=N'<SSISDB Folder/Project, sysname, SSIS Project Name>', @value=N'<Successful package FROM e-mail, sysname, etlserver@abc.def>', @data_type=N'String'
GO

EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'SMTP Package Success To Line', @sensitive=False, @description=N'Email address in To Line for package success', @environment_name=N'SSISFrameworkCommon', @folder_name=N'<SSISDB Folder/Project, sysname, SSIS Project Name>', @value=N'<Success package TO e-mail, sysname, SSISSuccess@abc.def>', @data_type=N'String'
GO

EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'SMTP Server Name', @sensitive=False, @description=N'Server name for SMTP server', @environment_name=N'SSISFrameworkCommon', @folder_name=N'<SSISDB Folder/Project, sysname, SSIS Project Name>', @value=N'<SMTP Server, sysname, localhost>', @data_type=N'String'
GO

EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'StagingDB Database Name', @sensitive=False, @description=N'Database name of Staging DB', @environment_name=N'SSISFrameworkCommon', @folder_name=N'<SSISDB Folder/Project, sysname, SSIS Project Name>', @value=N'<Staging DB Name, sysname, DWStaging>', @data_type=N'String'
GO

EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'StagingDB Instance Name', @sensitive=False, @description=N'Instance name of Staging DB', @environment_name=N'SSISFrameworkCommon', @folder_name=N'<SSISDB Folder/Project, sysname, SSIS Project Name>', @value=N'<Staging Instance, sysname, localhost>', @data_type=N'String'
GO


